﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using LumenWorks.Framework.IO.Csv;
using System.IO;
using System.Data.SQLite;


namespace trabalhoCSharp
{
    //Classe parcial Form1 - Interface gráfica
    public partial class Form1 : Form
    {
        private SQLiteConnection connection;
        private SQLiteCommand command;

        //lista de objectos para guardar todos os dados da base de dados SQLite
        private List<listObject> lista = new List<listObject>();

        //lista de strings para guardar os dados pedidos nas duas estatisticas
        private List<string> listaEstatistica1 = new List<string>();
        private List<string> listaEstatistica2 = new List<string>();
        private int opc;

        //Constructor da classe
        public Form1()
        {
            //inicializa os componentes da interface grafica
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {

        }

        //Chama o metodo createDatabase
        private void toolStripMenuItem2_Click(object sender, EventArgs e)
        {
            this.createDatabase();
        }

        //Sai da aplicação
        private void toolStripMenuItem3_Click(object sender, EventArgs e)
        {
            Environment.Exit(0);
        }

        //Cria uma conexão entre o C# e a base de dados SQLite
        private void SetConnection()
        {
            connection = new SQLiteConnection
                ("Data Source=Database.db;Version=3;New=False;Compress=True;");
        }


        private void estatistica1MenuItem_Click(object sender, EventArgs e)
        {
            /* criar estatistica - número de docentes por categoria no sistema de ensino superior por estabelecimento
             * limpa o conteudo da listbox1 e muda o valor da variavel para obter a estatistica pretendida
             */ 
            listBox1.Items.Clear();
            this.opc = 1;
            this.createList();

        }

        private void estatistica2MenuItem_Click(object sender, EventArgs e)
        {
            /* criar estatistica - número de docentes por tipo de grau por estabelecimento e por ano
             * limpa o conteudo da listbox1 e muda o valor da variavel para obter a estatistica pretendida
             */ 
            listBox1.Items.Clear();
            this.opc = 2;
            this.createList();
        }


        //cria a base de dados a partir dos ficheiros *.csv - Database.db
        private void createDatabase()
        {
            //abre a ligação à base de dados para que se possam fazer alterações na mesma - Database.db
            SetConnection();
            connection.Open();
            
            //Executa todos os querys no final do programa de forma a melhorar o tempo de execução da aplicação 
            SQLiteTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

            //cria o command  
            command = connection.CreateCommand();

            //apaga a tabela professores no caso de já existir
            command.CommandText = "drop table if exists professores";
            command.ExecuteNonQuery();

            //cria a tabela professores com respectivas colunas
            command.CommandText = "create table professores(id text, nome text, grau text, curso text, categoria text, regimeDeTempo text, anoLectivo text, tipoDeEnsino text, nomeDoEstabelecimento)";
            command.ExecuteNonQuery();

            //Ciclo for para percorrer os 11 ficheiros CSV
            for (int count = 0; count <= 10; count++)
            {
                // abre os ficheiros "reb*.csv" 
                using (CsvReader csv =
                       new CsvReader(new StreamReader("reb" + count + ".csv"), true))
                {
                    int fieldCount = csv.FieldCount;
                    string[] headers = csv.GetFieldHeaders();

                    while (csv.ReadNextRecord())
                    {
                        // Copia os dados (linha completa) para a tabela
                        command.CommandText = "insert into professores values ('" + csv[0].Replace("\'", "") + "','" + csv[1].Replace("\'", "") + "','" +
                            csv[2].Replace("\'", "") + "','" + csv[3].Replace("\'", "") + "','" + csv[4].Replace("\'", "") + "','" + csv[5].Replace("\'", "") +
                            "','" + csv[6].Replace("\'", "") + "','" + csv[7].Replace("\'", "") + "','" + csv[8].Replace("\'", "") + "')";
                        command.ExecuteNonQuery();
                    }
                }
            }
            transaction.Commit();
            connection.Close();
        }


        // Cria uma lista de objectos
        public void createList()
        {
            //Cria uma conexão e abre-a
            SetConnection();
            connection.Open();

            //cria o comando
            command = connection.CreateCommand();

            //selecciona a tabela professores e extrai o seu conteúdo
            command.CommandText = "SELECT * FROM professores";

            //Inicia a leitura da base de dados
            SQLiteDataReader datareader = command.ExecuteReader();

            // Adiciona informação à lista enquanto o leitor tiver conteúdos a ler
            while (datareader.Read())
            {
                lista.Add(new listObject((String)datareader[0], (String)datareader[1], (String)datareader[2],
                    (String)datareader[3], (String)datareader[4], (String)datareader[5], (String)datareader[6],
                    (String)datareader[7], (String)datareader[8]));
            }

            //opção para executar uma das duas estatisticas
            if (opc == 1)
            {
                criarEstatistica1();
            }
            else if (opc == 2)
            {
                criarEstatistica2();
            }
            connection.Close();
        }

        //cria a estatistica - número de docentes por categoria no sistema de ensino superior por estabelecimento
        public void criarEstatistica1()
        {
            //Query efectuada em LINQ. Agrupa as linhas segundo os campos pedidos 
            var a = from professores in this.lista
                    group professores by new { professores.categoria, professores.anoLectivo, professores.nomeDoEstabelecimento } into estatistica1

                    select new
                    {
                        categoria = estatistica1.Key.categoria,
                        anoLectivo = estatistica1.Key.anoLectivo,
                        nomeDoEstabelecimento = estatistica1.Key.nomeDoEstabelecimento,
                        Count = estatistica1.Count()
                    };

            /*percorre cada elemento da selecção que foi feita anteriormente e adiciona-o à lista.
             *Esta lista é colocada na listbox da interface
             */
            foreach (var i in a)
            {
                listBox1.Items.Add("Categoria: " + i.categoria + "      Ano Lectivo: " + i.anoLectivo +
                    "      Nome do Estabelecimento: " + i.nomeDoEstabelecimento +
                    "      Nº de Professores: " + i.Count);
            }
        }

        //cria a estatistica - o número de docentes por tipo de grau por estabelecimento e por ano
        public void criarEstatistica2()
        {
            //Query efectuada em LINQ. Agrupa as linhas segundo os campos pedidos 
            var b = from professores in this.lista
                    group professores by new { professores.grau, professores.anoLectivo, professores.nomeDoEstabelecimento } into estatistica1

                    select new
                    {
                        grau = estatistica1.Key.grau,
                        anoLectivo = estatistica1.Key.anoLectivo,
                        nomeDoEstabelecimento = estatistica1.Key.nomeDoEstabelecimento,
                        Count = estatistica1.Count()
                    };

            /*percorre cada elemento da selecção que foi feita anteriormente e adiciona-o à lista.
             *Esta lista é colocada na listbox da interface
             */
            foreach (var i in b)
            {
                listBox1.Items.Add("Grau: " + i.grau + "      Ano Lectivo: " + i.anoLectivo + "      Nome do Estabelecimento: " + i.nomeDoEstabelecimento +
                    "      Nº de Professores: " + i.Count);
            }
        }

    }
}
